﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using System.Web;
using HCQ2_Common;
using HCQ2_Common.DD;
using HCQ2_Common.Log;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.Data;
using HCQ2_Model.Pay;
using HCQ2_Model.ViewModel;

namespace HCQ2_DAL_MSSQL
{
    /// <summary>
    /// 订单相关
    /// </summary>
    public partial class N_OrderDAL: IN_OrderDAL
    {

        /// <summary>
        /// 获取购买商品信息
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public List<T_DocumentInfo> ListDocumentInfo(List<int> fileId)
        {
            string sql = $"select * from T_DocumentInfo where file_id in({string.Join(",",fileId)})";
            return SqlHelper.ExecuteList<T_DocumentInfo>(sql);
        }

        /// <summary>
        /// 获取购买商品的价格信息
        /// </summary>
        /// <param name="commodityInfos">购买商品ID、购买期限</param>
        /// <returns></returns>
        public List<PaymentAmountParamsCommodityDetailedInfo> ListPayCommodityDetailedInfo(List<PaymentAmountParamsCommodityInfo> commodityInfos)
        {
            List<string> where = new List<string>(commodityInfos.Count);
            foreach (PaymentAmountParamsCommodityInfo commodityInfo in commodityInfos) {
                where.Add($"(file_id={commodityInfo.CommodityId} and op_month={commodityInfo.PurchasePeriod.Value})");
            }

            string sql = $"select file_id CommodityId,op_month PurchasePeriod,ori_price OriPrice from T_VRGoodDisiPrice where {string.Join(" or ",where)}";

            List<PaymentAmountParamsCommodityDetailedInfo> result = SqlHelper.ExecuteList<PaymentAmountParamsCommodityDetailedInfo>(sql);

            return result;
        }

        /// <summary>
        /// 获取订单中商品ID和商品购买期限
        /// </summary>
        /// <param name="parentOrderCode"></param>
        /// <returns></returns>
        public List<PaymentAmountParamsCommodityInfo> ListPaymentAmountParamsCommodityInfo(string parentOrderCode)
        {
            string sql = string.Format(@"select a.PurchasePeriod,b.file_id CommodityId from N_Order a
                inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
                where a.ParentOrderCode='{0}'",parentOrderCode);

            return SqlHelper.ExecuteList<PaymentAmountParamsCommodityInfo>(sql);
        }

        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="listParams"></param>
        /// <returns></returns>
        /// <remarks>
        /// 2021-8-2：根据禅道需求ID=146，添加VRType参数处理，查询中有此参数那么添加此参数查询
        /// </remarks>
        public OperationResult<TableModel<List<OrderListModel>>> ListOrderList(GetOrderListParams listParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{listParams.userid}'").FirstOrDefault();

            string sql = @"select 
                a.OrderCode,                
                b.file_name FileName,
                b.create_name CreateFileUser,
                CONVERT(nvarchar(50),a.OrderCreateTime,120)OrderCreateTime,
                a.OrderCreateUser,
                a.PurchasePeriod,
                a.CommodityMoney,
                a.DiscountMoney,
                a.PreferentialAmount,
                a.PaymentAmount,
                a.PaymentMethod,
                CONVERT(nvarchar(50),a.PaymentTime,120)PaymentTime,
                CONVERT(nvarchar(50),a.ConfirmTime,120)ConfirmTime,
                CONVERT(nvarchar(50),a.EndTime,120) EndTime,
                a.OrderState,
                a.OrderSource,
                a.PaymentCode,
                a.ParentOrderCode,
                a.OrderSubmitUser,
                --新增
                b.file_id CommodityId,--商品ID
                c.file_url FileImgUrl,--略缩图
                b.alias_name AliasName,--别名
                b.folder_limit FolderLimit,--权限
                b.file_note FileNote,--简介
                case when b.pro_type=1 then 'VRPPT'
                when b.pro_type=2 then '创客'
                when b.pro_type=3 then '头记'
                when b.pro_type=4 then '校史馆' else '' end ProType,--产品类别  
                b.supplyType SupplyType,--是否自营
                case when b.page_type='VRManager' then '素材'
				when b.page_type='VRWorks' then '作品'
				when b.page_type='VRModule' then '组件'
                when b.page_type='VRBuilding' then '楼宇'
                when b.page_type='QDMeta' then '奇点元宇宙'
                when b.page_type='QDMetaProgram' then '应用'
                when b.page_type='QDMetaRoom' then '房间'
                when b.page_type='QDMaterial' then '元宇宙元素'
				when b.page_type='DDCircle' then '度度圈' else '' end PageType,--商品类别            
          
                case when b.page_type='VRManager' then e.code_name
                when b.page_type='VRWorks' then 
                case when b.pro_form=1 then 'VR'
                when b.pro_form=2 then '虚拟仿真'
                when b.pro_form=3 then 'AR' else '' end
                else '' end Format,--格式
                CONVERT(nvarchar(50),b.approve_time,120) ApproveTime,--审核通过时间
                CONVERT(nvarchar(50),b.create_time,120) CreateTime,--时间
                (select CONVERT(nvarchar,MAX(option_size)) from T_DocFileOptions where file_id=b.file_id)+ 'MB' FileSize, --文件大小
				b.Quantity,--数量
				b.StoreID,--店铺ID
				f.folder_name StoreName,--店铺名称
				a.Name,--收货人姓名
				a.Phone,--收货人电话
				a.Area,--所在地区
				a.Address,--详细地址
				a.TableNum,--桌号
				a.StorageStatus,--备货状态
				a.PackType,--包装类别，0=堂食，1=打包
                a.UnitPrice--商品单价
                 from N_Order a
                inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
                inner join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord)t where rn=1
                 )c on b.file_id=c.file_id
                left join T_ItemCodeMenum e on b.mater_type=e.code_value
				left join T_DocumentFolder f on b.StoreID=f.folder_id";

            List<string> wheres = new List<string>();

            //买入，查询订单创建人
            if (listParams.OrderType.Value == 1) {
                wheres.Add($"a.OrderCreateUserId={user.user_id}");
            }
            //卖出，查询文件创建人
            if (listParams.OrderType.Value == 2)
            {
                wheres.Add($"b.create_id={user.user_id}");
            }

            if (!string.IsNullOrEmpty(listParams.Search))
            {
                wheres.Add($"(a.OrderCode like '%{listParams.Search}%' or b.file_name like '%{listParams.Search}%' or a.OrderCreateUser like '%{listParams.Search}%')");
            }

            if (!string.IsNullOrEmpty(listParams.OrderCode)) {
                wheres.Add($"a.OrderCode like '%{listParams.OrderCode}%'");
            }
            if (!string.IsNullOrEmpty(listParams.ParentOrderCode))
            {
                wheres.Add($"a.ParentOrderCode like '%{listParams.ParentOrderCode}%'");
            }
            if (!string.IsNullOrEmpty(listParams.FileName))
            {
                wheres.Add($"b.file_name like '%{listParams.FileName}%'");
            }
            if (!string.IsNullOrEmpty(listParams.Purchaser))
            {
                wheres.Add($"a.OrderCreateUser like '%{listParams.Purchaser}%'");
            }
            if (listParams.ProType!=null)
            {
                wheres.Add($"b.pro_type={listParams.ProType.Value}");
            }
            if (listParams.PurchasePeriod!=null)
            {
                wheres.Add($"a.PurchasePeriod={listParams.PurchasePeriod.Value}");
            }
            if (listParams.PayMethod!=null)
            {
                wheres.Add($"a.PaymentMethod={listParams.PayMethod.Value}");
            }
            if (listParams.OrderState!=null)
            {
                wheres.Add($"a.OrderState={listParams.OrderState.Value}");
            }
            if (!string.IsNullOrEmpty(listParams.OrderSource))
            {
                wheres.Add($"a.OrderSource='{listParams.OrderSource}'");
            }
            //下单时间都不为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23) between '{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}' and '{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间不为空，结束时间为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd == null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)>='{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间为空，结束时间不为空
            if (listParams.OrderCreateTimeStart == null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)<='{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }

            //素材
            if (listParams.VRType != null && listParams.VRType == 0)
            {
                wheres.Add("b.page_type='VRManager'");
            }
            //作品
            if (listParams.VRType != null && listParams.VRType == 1)
            {
                wheres.Add("b.page_type='VRWorks'");
            }
            //组件
            if (listParams.VRType != null && listParams.VRType == 2)
            {
                wheres.Add("b.page_type='VRModule'");
            }
            //度度圈
            if (listParams.VRType != null && listParams.VRType == 3)
            {
                wheres.Add("b.page_type='DDCircle'");
            }
            //元宇宙
            if (listParams.VRType != null && listParams.VRType == 4)
            {
                wheres.Add("b.page_type in ('QDMetaProgram','QDMetaRoom','QDMaterial','VRBuilding')");
            }
            ////楼宇,2022-03-17，老谭说的 楼宇也是元宇宙
            //if (listParams.VRType != null && listParams.VRType == 5)
            //{
            //    wheres.Add("b.page_type='VRBuilding'");
            //}
            ////奇点元宇宙【应用】
            //if (listParams.VRType != null && listParams.VRType == 5)
            //{
            //    sql += "b.page_type='QDMetaProgram'";
            //}
            ////奇点元宇宙【房间】
            //if (listParams.VRType != null && listParams.VRType == 6)
            //{
            //    sql += " b.page_type='QDMetaRoom'";
            //}

            #region 需求《VLions需求规格_度度圈接口需求_订单及购物车-202100909.docx》添加

            if (listParams.StoreID != null) {
                wheres.Add($"b.StoreID={listParams.StoreID.Value}");
            }

            if (listParams.StorageStatus != null && listParams.StorageStatus.Count > 0) {
                wheres.Add($"a.StorageStatus in({string.Join(",", listParams.StorageStatus.ToArray())})");
            }

            if (listParams.SubmitType != null) {
                wheres.Add($"a.OrderType={listParams.SubmitType}");
            }

            if (listParams.FolderID != null) {
                wheres.Add($"f.folder_id={listParams.FolderID.Value}");
            }

            #endregion

            //付款开始时间，付款截止时间都不为空
            if (listParams.PayDateStart != null && listParams.PayDateEnd != null)
            {
                wheres.Add($"CONVERT(date,a.PaymentTime,23) between '{listParams.PayDateStart.Value.ToString("yyyy-MM-dd")}' and '{listParams.PayDateEnd.Value.ToString("yyyy-MM-dd")}'");
            }
            //付款开始时间不为空，付款截止时间为空
            if (listParams.PayDateStart != null && listParams.PayDateEnd == null)
            {
                wheres.Add($"CONVERT(date,a.PaymentTime,23)>='{listParams.PayDateStart.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间为空，结束时间不为空
            if (listParams.PayDateStart == null && listParams.PayDateEnd != null)
            {
                wheres.Add($"CONVERT(date,a.PaymentTime,23)<='{listParams.PayDateEnd.Value.ToString("yyyy-MM-dd")}'");
            }

            if (wheres.Count > 0) {
                sql = $"{sql} where {string.Join(" and ", wheres)}";
            }

            //查询的数据源
            List<OrderListModel> orderListModels;
            if (listParams.SortType == 0 || listParams.SortType == 1)
            {
                //默认倒序
                bool sort = false;
                if (listParams.SortType == 1)
                {
                    sort = true;
                }

                orderListModels = SqlHelper.ExecutePageList<OrderListModel>(listParams.page, listParams.rows, "OrderCreateTime", sql, sort);
            }
            else if (listParams.SortType == 2)
            {
                //按照付款时间  降序
                orderListModels = SqlHelper.ExecutePageList<OrderListModel>(listParams.page, listParams.rows, "PaymentTime", sql, true);
            }
            else {
                return new OperationResult<TableModel<List<OrderListModel>>>(false, "失败，错误的SortType属性值："+listParams.SortType, null);
            }
            

            int count = SqlHelper.GetCount(sql);

            #region 禅道173号需求添加内容

            foreach (var r in orderListModels) {
                sql = $"select SpecName,SpecValue from N_Spec where SpecType=1 and OrderCode='{r.OrderCode}'";
                //获取规格列表
                var spens = SqlHelper.ExecuteList<SpecParamModel>(sql);

                r.Specs.AddRange(spens);
            }
                    
            #endregion

            TableModel<List<OrderListModel>> tableModel = new TableModel<List<OrderListModel>>() {
                rows = orderListModels,
                total = count
            };

            return new OperationResult<TableModel<List<OrderListModel>>>(true, "获取数据成功", tableModel);
        }
        /// <summary>
        /// 获取父订单号列表
        /// </summary>
        /// <param name="listParams"></param>
        /// <returns></returns>
        /// <remarks>
        /// 修改记录<br/>
        /// 2021-9-15：添加需求《VLions需求规格_度度圈接口需求_订单及购物车-202100909.docx》的相关内容<br/>
        /// 2021-9-23：修改查询条件等相关内容，处理禅道174号需求。
        /// </remarks>
        public OperationResult<GetParentOrderListTableModel> ListParentOrderList(GetParentOrderListParams listParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{listParams.userid}'").FirstOrDefault();

            //默认倒序
            bool sortType = false;
            List<string> wheres = new List<string>();
            string vrTypeMappingPageType = string.Empty;
            if (listParams.SortType == 1) {
                sortType = true;
            }

            if (listParams.OrderState != null)
            {
                wheres.Add($"a.OrderState={listParams.OrderState.Value}");
            }
            if (listParams.StoreID != null)
            {
                wheres.Add($"b.StoreID='{listParams.StoreID.Value}'");
            }
            if (listParams.VRType != null && listParams.VRType.Value != 4) {
                
                if (listParams.VRType.Value == 0)
                {
                    vrTypeMappingPageType = "VRManager";
                }
                if (listParams.VRType.Value == 1)
                {
                    vrTypeMappingPageType = "VRWorks";
                }
                if (listParams.VRType.Value == 2)
                {
                    vrTypeMappingPageType = "VRModule";
                }
                if (listParams.VRType.Value == 3)
                {
                    vrTypeMappingPageType = "DDCircle";
                }               
                //if (listParams.VRType.Value == 5)
                //{
                //    vrTypeMappingPageType = "VRBuilding";
                //}
                
                wheres.Add($"b.page_type='{vrTypeMappingPageType}'");
            }
            if (listParams.VRType != null && listParams.VRType.Value == 4) {
                //元宇宙房间和应用
                wheres.Add($"b.page_type in ('QDMetaProgram','QDMetaRoom','QDMaterial','VRBuilding')");
            }
            if (listParams.StorageStatus != null && listParams.StorageStatus.Count > 0) {
                wheres.Add($"a.StorageStatus in ({string.Join(",", listParams.StorageStatus)})");
            }
            if (listParams.OrderType != null) {
                if (listParams.OrderType.Value == 1) {
                    //买入
                    wheres.Add($"a.OrderCreateUserId={user.user_id}");
                }
                if (listParams.OrderType.Value == 2)
                {
                    //卖出
                    wheres.Add($"b.create_id={user.user_id}");
                }                
            }
            if (!string.IsNullOrEmpty(listParams.Search)) {
                wheres.Add($"(a.OrderCode like '%{listParams.Search}%' or b.file_name like '%{listParams.Search}%' or a.OrderCreateUser like '%{listParams.Search}%')");
            }            
            if (listParams.SubmitType != null)
            {
                wheres.Add($"a.OrderType={listParams.SubmitType}");
            }

            if (listParams.FolderID != null)
            {
                //wheres.Add($"f.folder_id={listParams.FolderID.Value}");
                wheres.Add($"b.file_id in(select file_id from T_DocumentFolderRelation where folder_id={listParams.FolderID.Value})");
            }

            if (listParams.OrderReceivingUserId != null) {
                wheres.Add($"a.OrderReceivingUserId={listParams.OrderReceivingUserId.Value}");
            }

            if (listParams.IsOrderReceiving != null && listParams.IsOrderReceiving.Value) {
                wheres.Add("a.OrderReceivingUserId is not null");
            }
            if (listParams.IsOrderReceiving != null && !listParams.IsOrderReceiving.Value)
            {
                wheres.Add("a.OrderReceivingUserId is null");
            }
            if (listParams.PackType != null) 
            {
                wheres.Add($"a.PackType={listParams.PackType.Value}");
            }

            string sql = string.Format(@"select 
            a.ParentOrderCode,
            COUNT(a.ParentOrderCode)CommodityTotal,
            SUM(a.PaymentAmount)PaymentAmountTotal,
            (select top 1 OrderState from N_Order where ParentOrderCode=a.ParentOrderCode)OrderState,
			(select top 1 Name from N_Order where ParentOrderCode=a.ParentOrderCode)Name,--收货人姓名
			(select top 1 Phone from N_Order where ParentOrderCode=a.ParentOrderCode)Phone,--收货人电话
			(select top 1 Area from N_Order where ParentOrderCode=a.ParentOrderCode)Area,--所在地区
			(select top 1 Address from N_Order where ParentOrderCode=a.ParentOrderCode)Address,--详细地址
			(select top 1 TableNum from N_Order where ParentOrderCode=a.ParentOrderCode)TableNum,--桌号
			(select top 1 PackType from N_Order where ParentOrderCode=a.ParentOrderCode)PackType,--包装类别
            (select top 1 CONVERT(varchar(100),OrderCreateTime,120) from N_Order where ParentOrderCode=a.ParentOrderCode)OrderCreateTime,--下单时间
            (select top 1 StorageStatus from N_Order where ParentOrderCode=a.ParentOrderCode order by StorageStatus)StorageStatus,--备货状态	
			(select top 1 GpsX from N_Order where ParentOrderCode=a.ParentOrderCode)GpsX,--经度
			(select top 1 GpsY from N_Order where ParentOrderCode=a.ParentOrderCode)GpsY,--纬度
            (select gps_x from T_DocumentFolder where folder_id in(select StoreID from N_OrderCommodityInfo where OrderCode in (
			select top 1 OrderCode from N_Order where ParentOrderCode=a.ParentOrderCode
			)))StoreGpsX,
			(select gps_y from T_DocumentFolder where folder_id in(select StoreID from N_OrderCommodityInfo where OrderCode in (
			select top 1 OrderCode from N_Order where ParentOrderCode=a.ParentOrderCode
			)))StoreGpsY,
			(select user_name from T_User where user_id in (select top 1 OrderReceivingUserId from N_Order where ParentOrderCode=a.ParentOrderCode and OrderReceivingUserId is not null)) OrderReceivingUserName, --接单人用户名
			(select user_phone from T_User where user_id in (select top 1 OrderReceivingUserId from N_Order where ParentOrderCode=a.ParentOrderCode and OrderReceivingUserId is not null)) OrderReceivingPhone,--接单人手机号
			(select REPLACE(user_img,'~','{0}') from T_User where user_id in (select top 1 OrderReceivingUserId from N_Order where ParentOrderCode=a.ParentOrderCode and OrderReceivingUserId is not null)) OrderReceivingUserPhoto--接单人头像图片地址
             from N_Order a  
            left join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
            left join T_DocumentFolder f on b.StoreID=f.folder_id
            where {1} group by a.ParentOrderCode", HCQ2_Common.ConfigurationHelper.AppSetting("WebUrl"), string.Join(" and ", wheres));

            //获取所有子订单总数SQL
            string childTotalSql = string.Format(@"
            select COUNT(OrderCode) from (
                select 
                a.OrderCode
                 from N_Order a  
                left join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
                left join T_DocumentFolder f on b.StoreID=f.folder_id
            where {0} )t", string.Join(" and ", wheres));

            var parentOrderInfo = SqlHelper.ExecutePageList<ParentOrderListModel>(listParams.page,listParams.rows, "OrderCreateTime", sql, sortType);
            int total = SqlHelper.GetCount(sql);
            int childTotal = SqlHelper.ExecuteScalarInt32(childTotalSql);

            //获取父订单下面所有的子订单
            foreach (ParentOrderListModel parentOrderListModel in parentOrderInfo)
            {               
                sql = string.Format(@"select 
                a.OrderCode,
                b.file_id CommodityId,
                b.file_name FileName,
                b.file_note FileNote,
                REPLACE(c.file_url,'~','{0}')  FileImgUrl,
                b.supplyType SupplyType,
                a.PurchasePeriod,
                a.PaymentAmount,				
				b.Quantity,--数量
				 b.StoreID,--店铺ID
				 f.folder_name StoreName,--店铺名称
				 a.StorageStatus,--备货状态				  
                a.UnitPrice --商品单价
                 from N_Order a
                 left join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
                 left join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord
                   )t where rn=1
                 )c on b.file_id=c.file_id
				 left join T_DocumentFolder f on  b.StoreID=f.folder_id                
                 where {1}", HCQ2_Common.ConfigurationHelper.AppSetting("WebUrl"), string.Join(" and ", wheres) + $" and a.ParentOrderCode='{parentOrderListModel.ParentOrderCode}'");
                
                var childs = SqlHelper.ExecuteList<ParentOrderListModelChild>(sql);

                #region 禅道174号需求处理

                //获取商品需求规格
                foreach (var r in childs)
                {
                    sql = $"select SpecName,SpecValue from N_Spec where SpecType=1 and OrderCode='{r.OrderCode}'";
                    var specs = SqlHelper.ExecuteList<SpecParamModel>(sql);
                    r.Specs.AddRange(specs);
                }

                #endregion

                if (parentOrderListModel.GpsX != null && parentOrderListModel.GpsY != null
                    && parentOrderListModel.StoreGpsX != null && parentOrderListModel.StoreGpsY != null)
                {
                    parentOrderListModel.Distance = GetDistance(Convert.ToDouble(parentOrderListModel.GpsX), Convert.ToDouble(parentOrderListModel.GpsY)
                        , Convert.ToDouble(parentOrderListModel.StoreGpsX), Convert.ToDouble(parentOrderListModel.StoreGpsY));
                }
                parentOrderListModel.ChildOrders = childs;
            }

            GetParentOrderListTableModel tableModel = new GetParentOrderListTableModel()
            {
                total = total,
                rows = parentOrderInfo,
                ChildTotal = childTotal
            };

            return new OperationResult<GetParentOrderListTableModel>(true,"获取数据成功", tableModel);
        }

        /// <summary>
        /// 获取订单详细
        /// </summary>
        /// <param name="detailedParams"></param>
        /// <returns></returns>
        public OperationResult<GetOrderDetailedModel> GetOrderDetailed(GetOrderDetailedParams detailedParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{detailedParams.userid}'").FirstOrDefault();

            string sql = string.Format(@"select                 
                a.OrderCode,
                b.file_id CommodityId,
                b.file_name FileName,
                b.file_note FileNote,
                REPLACE(c.file_url,'~','{0}')  FileImgUrl,
                b.supplyType SupplyType,
                a.PurchasePeriod,
                a.PaymentAmount,
				b.Quantity,--数量
				 b.StoreID,--店铺ID
				 d.folder_name StoreName,--店铺名称
				 a.StorageStatus--备货状态
                 from N_Order a
                 left join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
                 left join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord
                   )t where rn=1
                 )c on b.file_id=c.file_id
				 left join T_DocumentFolder d on  b.StoreID=d.folder_id
                 where {1}", HCQ2_Common.ConfigurationHelper.AppSetting("WebUrl"), GetOrderDetailedWhere(detailedParams));
            
            var commoditys = SqlHelper.ExecuteList<GetOrderDetailedModelChild>(sql);

            if (commoditys == null || commoditys.Count == 0)
            {
                return new OperationResult<GetOrderDetailedModel>(true, "未找到订单详细数据", null);
            }

            #region 2021-9-16：禅道175号需求添加
            foreach (var r in commoditys) {
                string csql = $"select SpecName,SpecValue from N_Spec where SpecType=1 and OrderCode='{r.OrderCode}'";
                var rdata = SqlHelper.ExecuteList<SpecParamModel>(csql);
                r.Specs.AddRange(rdata);
            }

            #endregion

            //如果父单号为空，通过查询出数据中的子单号找到父单号
            if (string.IsNullOrEmpty(detailedParams.ParentOrderCode))
            {
                sql = $"select top 1 ParentOrderCode from N_Order where OrderCode='{commoditys[0].OrderCode}'";
                detailedParams.ParentOrderCode = SqlHelper.ExecuteScalarString(sql);
            }

            sql = string.Format(@"select top 1 
                 case when a.OrderState=4 and (select COUNT(*) from N_Order where ParentOrderCode=a.ParentOrderCode and a.OrderState=2)>=1 then 2 else OrderState end OrderState,
                 a.OrderCreateTime,
                 a.PaymentMethod,
                 a.PaymentTime,
				 a.Name,--收货人姓名
				 a.Phone,--收货人电话
				 a.Area,--所在地区
				 a.Address,--详细地址
				 a.TableNum,--桌号
				 a.PackType--包装类别
                from N_Order a  where a.ParentOrderCode='{0}'", detailedParams.ParentOrderCode);
            var detailedModels = SqlHelper.ExecuteList<GetOrderDetailedModel>(sql);

            if (detailedModels == null || detailedModels.Count == 0) {
                return new OperationResult<GetOrderDetailedModel>(true, "获取数据失败", null);
            }

            GetOrderDetailedModel detailedModel = detailedModels[0];
            //订单总价
            decimal orderTotalMoney = 0;
            //自营商品总金额
            decimal orderSupplyCommodityMoney = 0;
            foreach (var commodity in commoditys)
            {
                orderTotalMoney = orderTotalMoney + commodity.PaymentAmount.Value;
                if (commodity.SupplyType == 1)
                {
                    orderSupplyCommodityMoney = orderSupplyCommodityMoney + commodity.PaymentAmount.Value;
                }
            }

            //使用赠币扣减的金额
            decimal orderGiveMoney = orderSupplyCommodityMoney;

            //用户赠币小于自营商品总金额
            if (user.give_gold < orderSupplyCommodityMoney)
            {
                orderGiveMoney = user.give_gold;
            }

            GetOrderDetailedModel orderDetailedModel = new GetOrderDetailedModel();
            orderDetailedModel.ParentOrderCode = detailedParams.ParentOrderCode;
            orderDetailedModel.OrderTotalMoney = orderTotalMoney;
            orderDetailedModel.Commoditys = commoditys;
            orderDetailedModel.OrderGiveMoney = orderGiveMoney;
            orderDetailedModel.PaymentAmount = orderTotalMoney - orderGiveMoney;
            orderDetailedModel.OrderState = detailedModel.OrderState;
            orderDetailedModel.OrderCreateTime = detailedModel.OrderCreateTime;
            orderDetailedModel.PaymentMethod = detailedModel.PaymentMethod;
            orderDetailedModel.PaymentTime = detailedModel.PaymentTime;

            orderDetailedModel.Name = detailedModel.Name;
            orderDetailedModel.Phone = detailedModel.Phone;
            orderDetailedModel.Area = detailedModel.Area;
            orderDetailedModel.Address = detailedModel.Address;
            orderDetailedModel.TableNum = detailedModel.TableNum;
            orderDetailedModel.PackType = detailedModel.PackType;            

            return new OperationResult<GetOrderDetailedModel>(true,"获取订单详细成功",orderDetailedModel);
        }
        /// <summary>
        /// 获取PC订单列表
        /// </summary>
        /// <param name=""></param>
        /// <param name=""></param>
        /// <returns></returns>
        public OperationResult<TableModel> ListOrderList(GetPcOrderListParams listParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{listParams.userid}'").FirstOrDefault();

            string sql = @"select 
                a.OrderCode,
                b.pro_type ProType,
                b.file_name FileName,
                b.create_name CreateFileUser,
                CONVERT(nvarchar(50),a.OrderCreateTime,120)OrderCreateTime,
                a.OrderCreateUser,
                a.PurchasePeriod,
                a.CommodityMoney,
                a.DiscountMoney,
                a.PreferentialAmount,
                a.PaymentAmount,
                a.PaymentMethod,
                CONVERT(nvarchar(50),a.PaymentTime,120)PaymentTime,
                CONVERT(nvarchar(50),a.ConfirmTime,120)ConfirmTime,
                CONVERT(nvarchar(50),a.EndTime,120)EndTime,
                a.OrderState,
                a.OrderSource,
                a.PaymentCode,
                a.ParentOrderCode,
                a.OrderSubmitUser,
				b.Quantity,--数量
				a.Name,--收货人姓名
				a.Area,--所在地区
				a.Address,--详细地址
				a.TableNum--桌号
                 from N_Order a
                inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode";

            List<string> wheres = new List<string>();

            //买入，查询订单创建人
            if (listParams.OrderType.Value == 1)
            {
                wheres.Add($"a.OrderCreateUserId={user.user_id}");
            }
            //卖出，查询文件创建人
            if (listParams.OrderType.Value == 2)
            {
                wheres.Add($"b.create_id={user.user_id}");
            }

            if (!string.IsNullOrEmpty(listParams.Search))
            {
                wheres.Add($"(a.OrderCode like '%{listParams.Search}%' or b.file_name like '%{listParams.Search}%' or a.OrderCreateUser like '%{listParams.Search}%' or a.Name like '%{listParams.Search}%' or a.Address like '%{listParams.Search}%')");
            }
            if (listParams.ProType != null)
            {
                wheres.Add($"b.pro_type={listParams.ProType.Value}");
            }
            if (listParams.PurchasePeriod != null)
            {
                wheres.Add($"a.PurchasePeriod={listParams.PurchasePeriod.Value}");
            }
            if (listParams.PaymentMethod != null)
            {
                wheres.Add($"a.PaymentMethod={listParams.PaymentMethod.Value}");
            }
            if (listParams.OrderState != null)
            {
                wheres.Add($"a.OrderState={listParams.OrderState.Value}");
            }
            if (!string.IsNullOrEmpty(listParams.OrderSource))
            {
                wheres.Add($"a.OrderSource='{listParams.OrderSource}'");
            }

            //下单时间都不为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23) between '{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}' and '{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间不为空，结束时间为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd == null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)>='{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间为空，结束时间不为空
            if (listParams.OrderCreateTimeStart == null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)<='{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }

            if (wheres.Count > 0)
            {
                sql = $"{sql} where {string.Join(" and ", wheres)}";
            }
            
            List<OrderListPCModel> orderListModels = SqlHelper.ExecutePageList<OrderListPCModel>(listParams.page, listParams.rows, "OrderCreateTime", sql,false);
            int count = SqlHelper.GetCount(sql);

            return new OperationResult<TableModel>(true, "获取数据成功", new TableModel()
            {
                rows = orderListModels,
                total = count
            });
        }

        /// <summary>
        /// 获取PC订单列表导出数据源
        /// </summary>
        /// <param name="listParams"></param>
        /// <returns></returns>
        public DataTable ListOrderListExpData(GetPcOrderListParams listParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{listParams.userid}'").FirstOrDefault();

            string sql = @"select 
                a.OrderCode '订单号',
                case when b.pro_type=1 then 'VRPPT'
                when b.pro_type=2 then '创客'
                when b.pro_type=3 then '头记'
                when b.pro_type=4 then '校史馆' end '产品类别',
                b.file_name '商品名称',
                b.create_name '作者',
                CONVERT(nvarchar(50),a.OrderCreateTime,120)'下单时间',
                a.OrderCreateUser '购买人',
                case when a.PurchasePeriod=1 then '1个月'
                when a.PurchasePeriod=3 then '3个月'
                when a.PurchasePeriod=6 then '6个月'
                when a.PurchasePeriod=12 then '12个月' end '商品期限',
                a.CommodityMoney '商品金额',
                a.DiscountMoney '折后金额',
                a.PreferentialAmount '优惠金额',
                a.PaymentAmount '实付金额',
                case when a.PaymentMethod=1 then '金币'
                when a.PaymentMethod=2 then '微信'
                when a.PaymentMethod=3 then '支付宝' end '付款方式',
                CONVERT(nvarchar(50),a.PaymentTime,120)'付款时间',
                a.ConfirmTime '确认日期',
                a.EndTime '截止日期',
                case when a.OrderState=1 then '等待买家付款'
                when a.OrderState=2 then '买家已付款'
                when a.OrderState=3 then '卖家已确认'
                when a.OrderState=4 then '交易成功'
                when a.OrderState=5 then '交易关闭'
                when a.OrderState=6 then '退款中' end '订单状态',
                a.OrderSource '订单来源',
                a.PaymentCode '支付流水号',
                a.ParentOrderCode '父订单号',
                a.OrderSubmitUser '提单人'
                 from N_Order a
                inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode ";

            List<string> wheres = new List<string>();

            //买入，查询订单创建人
            if (listParams.OrderType.Value == 1)
            {
                wheres.Add($"a.OrderCreateUserId={user.user_id}");
            }
            //卖出，查询文件创建人
            if (listParams.OrderType.Value == 2)
            {
                wheres.Add($"b.create_id={user.user_id}");
            }

            if (!string.IsNullOrEmpty(listParams.Search))
            {
                wheres.Add($"(a.OrderCode like '%{listParams.Search}%' or b.file_name like '%{listParams.Search}%' or a.OrderCreateUser like '%{listParams.Search}%')");
            }
            if (listParams.ProType != null)
            {
                wheres.Add($"b.pro_type={listParams.ProType.Value}");
            }
            if (listParams.PurchasePeriod != null)
            {
                wheres.Add($"a.PurchasePeriod={listParams.PurchasePeriod.Value}");
            }
            if (listParams.PaymentMethod != null)
            {
                wheres.Add($"a.PaymentMethod={listParams.PaymentMethod.Value}");
            }
            if (listParams.OrderState != null)
            {
                wheres.Add($"a.OrderState={listParams.OrderState.Value}");
            }
            if (!string.IsNullOrEmpty(listParams.OrderSource))
            {
                wheres.Add($"a.OrderSource='{listParams.OrderSource}'");
            }

            //下单时间都不为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23) between '{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}' and '{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间不为空，结束时间为空
            if (listParams.OrderCreateTimeStart != null && listParams.OrderCreateTimeEnd == null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)>='{listParams.OrderCreateTimeStart.Value.ToString("yyyy-MM-dd")}'");
            }
            //下单开始时间为空，结束时间不为空
            if (listParams.OrderCreateTimeStart == null && listParams.OrderCreateTimeEnd != null)
            {
                wheres.Add($"CONVERT(date,a.OrderCreateTime,23)<='{listParams.OrderCreateTimeEnd.Value.ToString("yyyy-MM-dd")}'");
            }

            if (wheres.Count > 0)
            {
                sql = $"{sql} where {string.Join(" and ", wheres)}";
            }
            sql = $" {sql} order by a.OrderCreateTime desc";
            return SqlHelper.ExecuteDataTable(sql);
        }

        /// <summary>
        /// 获取订单数量
        /// </summary>
        /// <param name="orderState"></param>
        /// <returns></returns>
        public int GetOrderTotal(GetOrderTotalParams getOrderTotalParams)
        {
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{getOrderTotalParams.userid}'").FirstOrDefault();

            string sql = string.Format(@"select COUNT(*) from (
	            select COUNT(ParentOrderCode)OrderTotal from (
		            select 
		            --如果状态（交易成功）还有其他子订单(已付款)那么所有都是（已付款）
		            case when OrderState=4 and (select COUNT(*) from N_Order a where a.ParentOrderCode=ParentOrderCode and OrderState=2)>=1 then 2 else OrderState end OrderState,
		            ParentOrderCode
		            from N_Order where OrderCreateUserId={0} and OrderState={1}
	            )T group by ParentOrderCode
            ) TT",user.user_id,getOrderTotalParams.OrderState.Value);

            return Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
        }

        /// <summary>
        /// 获取订单当前状态
        /// </summary>
        /// <param name="parentOrderCode">父订单ID</param>
        /// <returns></returns>
        public Nullable<int> GetOrderState(string parentOrderCode)
        {
            string sql = $"select OrderState from N_Order where ParentOrderCode='{parentOrderCode}'";
            return Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
        }

        /// <summary>
        /// 取消订单
        /// </summary>
        /// <param name="parentOrderCode"></param>
        /// <returns></returns>
        public int CancelOrder(string parentOrderCode)
        {
            string sql = $"update N_Order set OrderState=5,CloseTime=GETDATE() where ParentOrderCode='{parentOrderCode}'";

            return Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
        }

        /// <summary>
        /// 验证商品是否存在未付款订单
        /// </summary>
        /// <param name="userId">用户</param>
        /// <param name="commodityId">商品ID</param>
        /// <returns>不为空 表示存在、为空表示 不存在</returns>
        public string CheckCommodityUnpaidOrder(int userId, int commodityId)
        {
            string sql = string.Format(@"select top 1 b.file_name from N_Order a
            inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
            where a.OrderState=1 and a.OrderCreateUserId={0} and b.file_id={1}", userId,commodityId);

            return Convert.ToString(SqlHelper.ExecuteScalar(sql));
        }

        public string CheckCommodityPay(int userId, int commodityId)
        {
            string sql = $@"select top 1 file_name fileName from N_OrderCommodityInfo where OrderCode in (
	            select OrderCode from N_Order where OrderCreateUserId={userId} and OrderState in (2,4) and EndTime >= GETDATE()
            ) and file_id={commodityId}";
            
            return Convert.ToString(SqlHelper.ExecuteScalar(sql));
        }

        public string CheckDevice24TermOfValidity(int userId, int commodityId)
        {
            string sql = $@"select top 1 file_name fileName from N_OrderCommodityInfo where OrderCode in (
	            select OrderCode from N_Order where OrderCreateUserId={userId} and OrderState=2 and dateadd(HOUR,24,PaymentTime)>GETDATE()
            ) and file_id={commodityId}";

            return SqlHelper.ExecuteScalarString(sql);
        }

        /// <summary>
        ///  获取用户-购买商品有效期截止时间
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public string GetPayEndTime(int userId, int fileId)
        {
            string sql = $@"SELECT TOP 1 CONVERT(varchar(100),EndTime,20)as EndTime FROM N_Order WHERE OrderCode IN(SELECT OrderCode FROM N_OrderCommodityInfo WHERE file_id = {fileId}) AND OrderCreateUserId = {userId} AND OrderState IN(2, 4) ORDER BY OrderCreateTime DESC ";

            return SqlHelper.ExecuteScalarString(sql);
        }

        public OrderStatisticsModel GetOrderStatistics(int userId, OrderStatisticsParams param)
        {
            List<string> where = new List<string>();

            if (param.StoreID != null)
            {
                where.Add($"StoreID={param.StoreID.Value}");
            }

            //素材
            if (param.VRType != null && param.VRType == 0)
            {
                where.Add("page_type='VRManager'");
            }

            //作品
            if (param.VRType != null && param.VRType == 1)
            {
                where.Add("page_type='VRWorks'");
            }
            //组件
            if (param.VRType != null && param.VRType == 2)
            {
                where.Add("page_type='VRModule'");
            }
            //度度圈
            if (param.VRType != null && param.VRType == 3)
            {
                where.Add("page_type='DDCircle'");
            }
            //楼宇
            if (param.VRType != null && param.VRType == 4)
            {
                where.Add("page_type='VRBuilding'");
            }
            //奇点元宇宙【应用】
            if (param.VRType != null && param.VRType == 5)
            {
                where.Add("page_type='QDMetaProgram'");
            }
            //奇点元宇宙【房间】
            if (param.VRType != null && param.VRType == 6)
            {
                where.Add("page_type='QDMetaRoom'");
            }
            /*
             1、待付款订单数		1（等待买家付款）
             2、待发货订单数		2（买家已付款）
             3、待收货订单数		7（卖家已发货）
             4、待评价订单数		4（交易成功）
             5、退款中订单数		6（退款中）
             */
            string sql = $"select OrderState,count(OrderState)num from N_Order where OrderCreateUserId={userId} group by OrderState";

            //店铺ID，不为空，需要查看用户指定店铺的订单
            if (param.StoreID != null || param.VRType != null)
            {
                sql = $@"select OrderState,count(OrderState)num from N_Order where OrderCreateUserId={userId} 
                and OrderCode in (select OrderCode from N_OrderCommodityInfo where {string.Join(" and ", where)} 
                and OrderCode in (select OrderCode from N_Order where OrderCreateUserId={userId}))
                group by OrderState";
            }

            DataTable data = SqlHelper.ExecuteDataTable(sql);

            OrderStatisticsModel statisticsModel = new OrderStatisticsModel();
            if (data.Rows.Count == 0) {
                return statisticsModel;
            }

            foreach (DataRow dataRow in data.Rows) {
                int orderState = Convert.ToInt32(dataRow["OrderState"]);
                int num = Convert.ToInt32(dataRow["num"]);

                if (orderState == 1) {
                    statisticsModel.Order1 = num;
                }
                if (orderState == 2) {
                    statisticsModel.Order2 = num;
                }
                if (orderState == 7) 
                {
                    statisticsModel.Order3 = num;
                }
                if (orderState == 4) {
                    statisticsModel.Order4 = num;
                }
                if (orderState == 6) {
                    statisticsModel.Order5 = num;
                }
            }

            return statisticsModel;
        }

        public OperationResult UpdateStorageStatus(UpdateStorageStatusParams param)
        {
            string orderCode;
            N_Order dbOrder;
            //父订单号和订单号都不为空，优先订单号,父单号为空,订单号不为空
            bool b = (!string.IsNullOrWhiteSpace(param.ParentOrderCode)
                && !string.IsNullOrWhiteSpace(param.OrderCode))
                || (string.IsNullOrWhiteSpace(param.ParentOrderCode)
                && !string.IsNullOrWhiteSpace(param.OrderCode));
            if (b)
            {
                dbOrder = SqlHelper.FindSingle<N_Order>($"select top 1 * from N_Order where OrderCode='{param.OrderCode}'");
                orderCode = param.OrderCode;
            }
            else {
                dbOrder = SqlHelper.FindSingle<N_Order>($"select top 1 * from N_Order where ParentOrderCode='{param.ParentOrderCode}'");
                orderCode = param.ParentOrderCode;
            }

            int userId = SqlHelper.ExecuteScalarInt32($"select user_id from T_User where user_guid='{param.userid}'");

            string sql = $"update N_Order set StorageStatus={param.StorageStatus} where OrderCode='{param.OrderCode}';";
            sql = sql + $@"insert into N_OrderStorageStatusLog
            (UserId, OrderCode, CreateUserId, CreateTime, BeforeState, AfterState)
            values
            ({userId}, '{orderCode}', {userId}, GETDATE(), {dbOrder.StorageStatus}, {param.StorageStatus.Value}); ";
            
            using (TransactionScope transactionScope = new TransactionScope()) {
                SqlHelper.ExecuteNonQuery(sql);

                transactionScope.Complete();
            }

            //订单备货状态变为2时，给店铺的服务员发一条推送消息，备货完成。
            OperationResult operationResult = SendMessWaiter(param, orderCode);
            return new OperationResult(true, "更新订单备货状态成功，更新状态为：" + param.StorageStatus+ "，" + operationResult.Message);
        }

        /// <summary>
        /// 备货完成，给服务员推送消息
        /// </summary>
        /// <param name="param">更新备货状态接口的参数</param>
        /// <param name="orderCode">订单</param>
        /// <returns></returns>
        public OperationResult SendMessWaiter(UpdateStorageStatusParams param, string orderCode) {
            if (param.StorageStatus != 2) {
                return new OperationResult(true, "未推送消息，备货状态不是2备货完成");
            }
            LogHelper.ErrorLog(typeof(N_OrderDAL),"订单备货完成，给服务员推送消息，参数："+ JsonHelper.ObjectToJson(param));
            //获取店铺信息
            string sql = $@"select folder_id storeId,folder_name storeName,folder_url from T_DocumentFolder where folder_id in (
select storeId from N_OrderCommodityInfo where 
OrderCode in (select OrderCode from N_Order where StorageStatus=2 and ParentOrderCode='{orderCode}' or StorageStatus=2 and OrderCode='{orderCode}'))";
            
            DataTable dataTable = SqlHelper.ExecuteDataTable(sql);
            //没有获取到店铺信息
            if (dataTable == null || dataTable.Rows.Count == 0) {
                return new OperationResult(false, "未推送消息，没有获取到店铺信息");
            }

            //店铺ID
            int storeId = Convert.ToInt32(dataTable.Rows[0]["storeId"]);
            //店铺名称
            string storeName = Convert.ToString(dataTable.Rows[0]["storeName"]);
            //店铺logo
            string folder_url = Convert.ToString(dataTable.Rows[0]["folder_url"]);
            string logo = !string.IsNullOrEmpty(folder_url) ? folder_url?.Replace("~", HttpContext.Current.Request.Url.AbsoluteUri.Substring(0, HttpContext.Current.Request.Url.AbsoluteUri.IndexOf("/api/"))) : "";

            //获取店铺服务员
            sql = $@"SELECT t.store_type,u.user_id,u.user_name,u.user_phone,u.user_guid,i.code_note FROM 
(SELECT dd_id,store_type FROM dbo.T_VRDDStoreTypeUser WHERE store_id={storeId}
 and store_type='waiter')t INNER JOIN
(SELECT user_id,user_phone,user_name,user_guid FROM dbo.T_User)u ON t.dd_id=u.user_id LEFT JOIN
(SELECT code_value,code_note FROM dbo.T_ItemCodeMenum WHERE item_id IN(
SELECT item_id FROM dbo.T_ItemCode WHERE item_code='StaffType')
)i ON t.store_type=i.code_value";

            DataTable dataTable2 = SqlHelper.ExecuteDataTable(sql);
            //店铺没有服务员则不处理
            if (dataTable2 == null || dataTable2.Rows.Count == 0) {
                return new OperationResult(false, "未推送消息，店铺没有服务员");
            }

            //获取当前操作的用户
            T_User user = SqlHelper.ExecuteList<T_User>($"select * from T_User where user_guid='{param.userid}'").FirstOrDefault();

            //消息内容
            string messContent = Convert.ToString(dataTable2.Rows[0]["code_note"]);
            
            //插入给服务员推送的消息，返回消息ID
            int messId = InsertSendMess(user, dataTable2.Rows.Count, storeId, storeName, messContent,2);

            //push_status推送状态：-1未推送、1已推送 
            foreach (DataRow row in dataTable2.Rows) {
                //接收人ID                
                int acceptId = Convert.ToInt32(row["user_id"]);
                //接收人姓名
                string acceptName = Convert.ToString(row["user_name"]);
                //接收人电话
                string acceptPhone = Convert.ToString(row["user_phone"]);
                //接收人guid
                string acceptUserGuid = Convert.ToString(row["user_guid"]);
                //插入推送人
                int sendUserId = InsertSendUser(messId, user, storeId, storeName, acceptId, acceptName, acceptPhone);
                
                IMResult imResult = SendIM(logo, user.user_phone, acceptUserGuid, acceptPhone, storeId, storeName, messContent, sendUserId);

                if (imResult.code == 200)
                {
                    //推送成功，更新推送时间和推送状态为：已推送
                    SqlHelper.ExecuteNonQuery($@"update T_VRDDSendUser set push_status=1,push_date=getdate() where id={sendUserId};");
                    //更新推送消息，为已推送
                    SqlHelper.ExecuteNonQuery($"update T_VRDDSendMess set send_status=1 where mess_id={messId};");
                }
                else {
                    //推送失败
                    return new OperationResult(false, "推送消息失败，网易云信返回状态："+ imResult.code+"，返回信息："+ imResult.desc);
                }
            }

            LogHelper.ErrorLog(typeof(N_OrderDAL), "订单备货完成，给服务员推送消息完成，参数：" + JsonHelper.ObjectToJson(param));
            return new OperationResult(true, "已给服务员推送消息备货完成。");
        }

        /// <summary>
        /// 插入消息返回主键id
        /// </summary>
        /// <param name="user">发送用户</param>
        /// <param name="send_num">推送人数</param>
        /// <param name="storeId">店铺ID</param>
        /// <param name="storeName">店铺名称</param>
        /// <param name="messContent">消息内容</param>
        /// <param name="mess_type">禅道上面mess_type写的：厨师=1，服务员=2，骑手=3</param>
        /// <returns>返回插入的主键ID</returns>
        private int InsertSendMess(T_User user, int send_num, int storeId, string storeName, string messContent, int mess_type) 
        {
            string sql = $@"insert into T_VRDDSendMess (send_id,send_name,send_status,send_num,mess_type,
store_id,store_name,mess_note,create_id,create_name,update_id,update_name,update_date)
values
({user.user_id},'{user.user_name}',-1,{send_num},{mess_type},
{storeId},'{storeName}','{messContent}',{user.user_id},'{user.user_name}',{user.user_id},'{user.user_name}',getDate());SELECT SCOPE_IDENTITY();";

            //插入推送消息，返回消息ID
            int messId = SqlHelper.ExecuteScalarInt32(sql);
            return messId;
        }

        /// <summary>
        /// 插入消息推送的用户
        /// </summary>
        /// <param name="messId">消息ID</param>
        /// <param name="user">创建用户</param>
        /// <param name="storeId">店铺ID</param>
        /// <param name="storeName">店铺名称</param>
        /// <param name="acceptId">接收者ID</param>
        /// <param name="acceptName">接收者名称</param>
        /// <param name="acceptPhone">接收者电话</param>
        /// <returns>返回插入的主键ID</returns>
        private int InsertSendUser(int messId, T_User user, int storeId, string storeName, int acceptId, string acceptName, string acceptPhone)
        {
            string sql = $@"insert into T_VRDDSendUser (mess_id,create_id,create_name,
store_id,store_name,push_status,
accept_id,accept_name,accept_phone,accept_status)
values
({messId},{user.user_id},'{user.user_name}',{storeId},'{storeName}',-1,{acceptId},'{acceptName}','{acceptPhone}',-1);SELECT SCOPE_IDENTITY();";

            //插入推送人主键
            int id = SqlHelper.ExecuteScalarInt32(sql);
            return id;
        }

        /// <summary>
        /// 发起
        /// </summary>
        /// <param name="logo">店铺logo</param>
        /// <param name="sendUserPhone">发送用户电话</param>
        /// <param name="acceptUserGuId">接收用户guid</param>
        /// <param name="acceptPhone">接收用户电话</param>
        /// <param name="storeId">店铺ID</param>
        /// <param name="storeName">店铺名称</param>
        /// <param name="messContent">消息内容</param>
        /// <param name="messUserId">推送用户信息ID</param>
        /// <returns>返回网易云信 IMResult 对象</returns>
        private IMResult SendIM(string logo, string sendUserPhone, string acceptUserGuId, string acceptPhone, int storeId, string storeName, string messContent, int messUserId) 
        {
            StringBuilder attach = new StringBuilder();
            attach.Append("{");
            attach.Append("\"logo\":\"" + logo + "\",");//店铺logo地址
            attach.Append("\"userid\":\"" + acceptUserGuId + "\",");//接收人用户编码
            attach.Append("\"storeName\":\"" + storeName + "\",");//店铺名
            attach.Append("\"messNote\":\"" + messContent + "\",");//消息内容
            attach.Append("\"messType\":\"" + 2 + "\",");//消息类别
            attach.Append("\"storeId\":\"" + storeId + "\",");//店铺ID
            attach.Append("\"messUserId\":\"" + messUserId + "\"");//推送用户列表主键值
            attach.Append("}");
            string imresult = IMUtil.ExecuteRequest(8, "from=" + sendUserPhone + "&msgtype=0&to=" + acceptPhone + $"&attach={attach.ToString()}");
            IMResult imResult = JsonHelper.JsonStrToObject<IMResult>(imresult);
            return imResult;
        }

        public OperationResult UpdateOrderReceivingUser(UpdateOrderReceivingUserParams param)
        {
            string sql = $"select user_id from T_User where user_guid='{param.userid.Trim()}'";
            Object userId = SqlHelper.ExecuteScalar(sql);
            if (userId == null) {
                return new OperationResult(false, "未找到用户信息");
            }

            sql = $"update N_Order set OrderReceivingUserId={userId},OrderReceivingTime=GETDATE() where ParentOrderCode='{param.ParentOrderCode.Trim()}'";
            SqlHelper.ExecuteNonQuery(sql);

            return new OperationResult(true, "保存接单人信息成功");
        }

        public OperationResult GetParentGroupOrderTotal(GetParentGroupOrderTotalParams param)
        {
            List<string> wheres = new List<string>();
            if (param.StorageStatus != null && param.StorageStatus.Count > 0) {
                wheres.Add($"a.StorageStatus in ({string.Join(",", param.StorageStatus.ToArray())} )");
            }            

            if (param.StoreID != null) {
                wheres.Add($"b.StoreID ={param.StoreID.Value}");
            }

            //下面的条件有问题，OrderReceivingUserId和IsOrderReceiving应该不能同时有值，那样存在错误
            if (param.OrderReceivingUserId != null) {
                wheres.Add($"a.OrderReceivingUserId={param.OrderReceivingUserId.Value}");
            }

            if (param.IsOrderReceiving != null && param.IsOrderReceiving.Value) {
                wheres.Add("a.OrderReceivingUserId is not null");
            }

            if (param.IsOrderReceiving != null && !param.IsOrderReceiving.Value)
            {
                wheres.Add("a.OrderReceivingUserId is null");
            }

            if (param.FolderID != null) {
                wheres.Add($"b.file_id in(select file_id from T_DocumentFolderRelation where folder_id={param.FolderID.Value})");
            }

            if (param.OrderState != null)
            {
                wheres.Add($"a.OrderState={param.OrderState.Value}");
            }
            if (param.PackType != null)
            {
                wheres.Add($"a.PackType={param.PackType.Value}");
            }
            if (param.SubmitType != null)
            {
                wheres.Add($"a.OrderType={param.SubmitType}");
            }
            
            if (param.VRType != null && param.VRType != 4)
            {
                string vrTypeMappingPageType = string.Empty;
                if (param.VRType.Value == 0)
                {
                    vrTypeMappingPageType = "VRManager";
                }
                if (param.VRType.Value == 1)
                {
                    vrTypeMappingPageType = "VRWorks";
                }
                if (param.VRType.Value == 2)
                {
                    vrTypeMappingPageType = "VRModule";
                }
                if (param.VRType.Value == 3)
                {
                    vrTypeMappingPageType = "DDCircle";
                }
                //if (param.VRType.Value == 5)
                //{
                //    vrTypeMappingPageType = "VRBuilding";
                //}
                
                wheres.Add($"b.page_type='{vrTypeMappingPageType}'");
            }
            if (param.VRType != null && param.VRType == 4) {
                wheres.Add($"b.page_type in ('QDMetaProgram','QDMetaRoom','QDMaterial','VRBuilding')");
            }

            string sql = $@"select COUNT(t) from (
            select COUNT(ParentOrderCode)t from N_Order a
            inner join N_OrderCommodityInfo b on a.OrderCode = b.OrderCode
            where {string.Join(" and ", wheres)}
            group by ParentOrderCode
            )pp";

            int total = SqlHelper.ExecuteScalarInt32(sql);
            return new OperationResult(true, "获取数据成功", total);
        }

        public GetStoreOrderStatModel GetStoreOrderStat(GetStoreOrderStatParams param)
        {
            string sd = param.PayDateStart.Value.ToString("yyyy-MM-dd");
            string ed = param.PayDateEnd.Value.ToString("yyyy-MM-dd");

            string sql = $@"select 
--子订单数合计
(select COUNT(OrderCode) from N_Order where OrderState=2 and CONVERT(date,PaymentTime,23) between '{sd}' and '{ed}' and OrderCode in (select OrderCode from N_OrderCommodityInfo where StoreID={param.StoreID})) TotalOrderNumber,

--付款人去重的数量合计
(select COUNT(DISTINCT OrderCreateUserId) from N_Order where OrderState=2 and  CONVERT(date,PaymentTime,23) between '{sd}' and '{ed}' and OrderCode in (select OrderCode from N_OrderCommodityInfo where StoreID={param.StoreID})) TotalPayerNumber,

--商品去重的数量合计
(select count(distinct file_id) from N_OrderCommodityInfo where StoreID={param.StoreID} and OrderCode in (select OrderCode from N_Order where OrderState=2 and CONVERT(date,PaymentTime,23) between '{sd}' and '{ed}'))TotalGoodsNumber, 

--实付金额合计
(select sum(PaymentAmount) from N_Order where OrderState=2 and CONVERT(date,PaymentTime,23) between '{sd}' and '{ed}' and OrderCode in (select OrderCode from N_OrderCommodityInfo where StoreID={param.StoreID})) TotalAmount,

--商品销售数量合计
(select sum(Quantity) from N_Order where OrderState=2 and CONVERT(date,PaymentTime,23) between '{sd}' and '{ed}' and OrderCode in (select OrderCode from N_OrderCommodityInfo where StoreID={param.StoreID})) TotalQuantity";

            GetStoreOrderStatModel model = SqlHelper.ExecuteList<GetStoreOrderStatModel>(sql)[0];

            return model;
        }

        public List<GetStoreGoodsStatModel> GetStoreGoodsStat(GetStoreGoodsStatParams parsm)
        {
            string sd = parsm.PayDateStart.Value.ToString("yyyy-MM-dd");
            string ed = parsm.PayDateEnd.Value.ToString("yyyy-MM-dd");
            
            string orderSql = string.Empty;
           
            if (parsm.SortType == 0)
            {
                //0：按 销售数量降序、实收金额降序、商品名称升序 排序
                orderSql = "order by Quantity desc,Amount desc,FileName";
            }
            else if (parsm.SortType == 1) {
                //1:按 实收金额降序、销售数量降序、商品名称升序 排序
                orderSql = "order by Amount desc, Quantity desc,FileName";
            }

            string sql = $@"select 
	b.file_id FileId,
	b.file_name FileName,
	c.file_url FileImage,
	a.OrderCode,
	a.UnitPrice,
	a.Quantity,
	a.PaymentAmount 
	into #tempSelect from N_Order a
	inner join N_OrderCommodityInfo b on a.OrderCode=b.OrderCode
	left join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord)t where rn=1
	)c on b.file_id=c.file_id
where a.OrderState=2 and a.PaymentTime between '{sd}' and '{ed}' and b.StoreID={parsm.StoreID} Order by a.PaymentTime desc

select * from (
select 
FileId,
(select top 1 FileName from #tempSelect where FileId=T.FileId)FileName,
(select top 1 FileImage from #tempSelect where FileId=T.FileId)FileImage,
(select top 1 UnitPrice from #tempSelect where FileId=T.FileId)UnitPrice,
(select sum(Quantity) from #tempSelect where FileId=T.FileId)Quantity,
(select sum(PaymentAmount) from #tempSelect where FileId=T.FileId)Amount
 from #tempSelect t group by FileId
) tt {orderSql}
drop table #tempSelect";

            List<GetStoreGoodsStatModel> models = SqlHelper.ExecuteList<GetStoreGoodsStatModel>(sql);

            return models;
        }

        #region 私有方法

        /// <summary>
        /// 获取订单详细where条件
        /// </summary>
        /// <param name="detailedParams"></param>
        /// <returns></returns>
        private string GetOrderDetailedWhere(GetOrderDetailedParams detailedParams)
        {
            List<string> where = new List<string>(3);

            if (!string.IsNullOrEmpty(detailedParams.ParentOrderCode))
            {
                where.Add($"a.ParentOrderCode='{detailedParams.ParentOrderCode}'");
            }
            else {
                where.Add($"b.file_code='{detailedParams.FileCode}'");
                where.Add($"a.OrderState={detailedParams.OrderState}");
            }

            return string.Join(" and ",where);
        }


        //地球半径，单位米
        private const double EARTH_RADIUS = 6378137;
        /// <summary>
        /// 计算两点位置的距离，返回两点的距离，单位 米
        /// 该公式为GOOGLE提供，误差小于0.2米
        /// </summary>
        /// <param name="lat1">第一点纬度</param>
        /// <param name="lng1">第一点经度</param>
        /// <param name="lat2">第二点纬度</param>
        /// <param name="lng2">第二点经度</param>
        /// <returns></returns>
        public static double GetDistance(double lat1, double lng1, double lat2, double lng2)
        {
            double radLat1 = Rad(lat1);
            double radLng1 = Rad(lng1);
            double radLat2 = Rad(lat2);
            double radLng2 = Rad(lng2);
            double a = radLat1 - radLat2;
            double b = radLng1 - radLng2;
            double result = 2 * Math.Asin(Math.Sqrt(Math.Pow(Math.Sin(a / 2), 2) + Math.Cos(radLat1) * Math.Cos(radLat2) * Math.Pow(Math.Sin(b / 2), 2))) * EARTH_RADIUS;
            return result;
        }

        /// <summary>
        /// 经纬度转化成弧度
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        private static double Rad(double d)
        {
            return (double)d * Math.PI / 180d;
        }
        #endregion

    }
}
